package controller;

import java.sql.*;
import java.util.*;
import java.util.regex.*;

import utility.*;
import utility.DDLException.*;
import utility.DMLException.*;
import metadata.*;

/**
 * Relational DataBase Management System<br>
 * This class is to manage a database. <br>
 * <br>
 * To create a database, simply instantiate this class.<br>
 * All alterations to a database must be done via the RDBMS object managing it.<br>
 * <br>
 * To drop a database, simply delete the RDBMS object.<br>
 * 
 */
public class RDBMS {
	
	//public static vars
	public static final String PATTERN_NOT_ALPHANUMERIC				= "[^a-zA-Z0-9]";
	public static final String PATTERN_DISSALOWED_CHARS_FOR_NAMES	= "[^a-zA-Z0-9_]";
	
	public static final String TEMP_SUFFIX_DATABASENAME				= "_TEMP";
	
	//private vars
	private final Database database;
	
	
	//constructors
	/**
	 * Creates an empty database to be managed by this class. <br>
	 * 
	 * @param databaseName The name of the new database.
	 * @see metadata.Database
	 */
	public RDBMS(String databaseName) throws DDLException {
		checkIfNameValid(databaseName, "Database", PATTERN_NOT_ALPHANUMERIC);
		
		database = new Database(databaseName); 
	}
	
	
	//Public setup methods
	/**
	 * Extract a database structure from a database via a java.sql.Connection object. <br>
	 *  
	 * @param connection The connection from which to retrieve the database structure.
	 * @throws SQLException Thrown when retrieving data from the given database connection creates an Exception.
	 * @throws DDLException Thrown when there is an inconsistency present in the stored database structure, prevented by the RDBMS' database integrity checks.
	 */
	public void loadDatabase(Connection connection) throws SQLException, DDLException {

		//tables & columns
		ResultSet rsTables = connection.prepareStatement(
				"SELECT * FROM TBL WHERE DBNAME = '"+database.getName()+"'").executeQuery();
		while(rsTables.next()) {
			String tableName = rsTables.getString("NAME");
			
			List<String> columnNames 		= new ArrayList<String>();
			List<String> columnDatatypes 	= new ArrayList<String>();
			List<Boolean> columnNotnulls 	= new ArrayList<Boolean>();
			
			ResultSet rsColumns = connection.prepareStatement(
					"SELECT * FROM COL WHERE DBNAME = '"+database.getName()+"' AND TBLNAME = '"+tableName+"'").executeQuery();
			while(rsColumns.next()) {
				columnNames		.add(rsColumns.getString("NAME"));
				columnDatatypes	.add(rsColumns.getString("DATATYPE"));
				columnNotnulls	.add(rsColumns.getBoolean("NOTNULL"));
			}
			rsColumns.close();
			
			createTable(tableName, 
					columnNames.toArray(new String[0]),
					columnDatatypes.toArray(new String[0]),
					columnNotnulls.toArray(new Boolean[0]));
		}
		rsTables.close();
		
		
		//keys
		ResultSet rsKeys = connection.prepareStatement(
				"SELECT * FROM KEE WHERE DBNAME = '"+database.getName()+"'").executeQuery();
		while(rsKeys.next()) {
			String keyName = rsKeys.getString("NAME");
			String tableName = rsKeys.getString("TBLNAME");
			
			Map<Integer, String> columnNames = new HashMap<Integer, String>();
			
			ResultSet rsKeyColumns = connection.prepareStatement(
					"SELECT * FROM KEECOL WHERE DBNAME = '"+database.getName()+"' AND KEENAME = '"+keyName+"'").executeQuery();
			while(rsKeyColumns.next()) {
				columnNames.put(
						rsKeyColumns.getInt("SEQNR"),
						rsKeyColumns.getString("COLNAME"));
			}
			rsKeyColumns.close();
			
			String[] columnNamesArray = new String[columnNames.size()];
			for(int i = 0; i < columnNames.size(); ++i) {
				columnNamesArray[i] = columnNames.get(i);
			}
			
			createKey(keyName,
					tableName,
					columnNamesArray);
		}
		rsKeys.close();
		
		
		//primary keys
		ResultSet rsPrimaryKeys = connection.prepareStatement(
				"SELECT * FROM PKEE WHERE DBNAME = '"+database.getName()+"'").executeQuery();
		while(rsPrimaryKeys.next()) {
			String keyName 		= rsPrimaryKeys.getString("KEENAME");
			String tableName 	= rsPrimaryKeys.getString("TBLNAME");
			
			createPrimaryKey(keyName, tableName);
		}
		rsPrimaryKeys.close();
		
				
		//foreign keys
		ResultSet rsForeignKeys = connection.prepareStatement(
				"SELECT * FROM FKEE WHERE DBNAME = '"+database.getName()+"'").executeQuery();
		while(rsForeignKeys.next()) {
			String foreignKeyName 	= rsForeignKeys.getString("NAME");
			String tableName 		= rsForeignKeys.getString("TBLNAME");
			String toKeyName 		= rsForeignKeys.getString("TOKEENAME");
			
			Map<Integer, String> columnNames = new HashMap<Integer, String>();
			
			ResultSet rsForeignKeyColumns = connection.prepareStatement(
					"SELECT * FROM FKEECOL WHERE DBNAME = '"+database.getName()+"' AND FKEENAME = '"+foreignKeyName+"'").executeQuery();
			while(rsForeignKeyColumns.next()) {
				columnNames.put(
						rsForeignKeyColumns.getInt("SEQNR"),
						rsForeignKeyColumns.getString("COLNAME"));
			}
			rsForeignKeyColumns.close();
			
			String[] columnNamesArray = new String[columnNames.size()];
			for(int i = 0; i < columnNames.size(); ++i) {
				columnNamesArray[i] = columnNames.get(i);
			}
			
			createForeignKey(foreignKeyName,
					tableName,
					columnNamesArray,
					toKeyName);
		}
		rsForeignKeys.close();
	}
	
	/**
	 * Extract a database structure from a database via a java.sql.Connection object. <br>
	 * <br>
	 * It is not advised to use this method, use loadDatabase(Connection) instead.<br>
	 * That method can throw DDLExceptions for when there are inconsistencies in the stored database.<br>
	 * This method circumvents RDBMS' database integrity checks.<br>
	 * This method will stay present until metadatamodel changes drastically, for testing purposes.<br> 
	 *  
	 * @deprecated Use loadDatabase instead to ensure database integrity
	 * @param connection The connection from which to retrieve the database structure.
	 * @throws SQLException Thrown when retrieving data from the given database connection creates an Exception.
	 */
	public void loadDatabaseNative(Connection connection) throws SQLException {

		//tables
		ResultSet rs = connection.prepareStatement("SELECT * FROM TBL WHERE DBNAME = '"+database.getName()+"'").executeQuery();
		while(rs.next()) {
			Table table = new Table(rs.getString("NAME"));
			database.addTable(table);
		}
		
		//columns
		rs = connection.prepareStatement("SELECT * FROM COL WHERE DBNAME = '"+database.getName()+"'").executeQuery();
		while(rs.next()) {
			Column column = new Column(
				rs.getString("NAME"),
				rs.getString("DATATYPE"),
				rs.getBoolean("NOTNULL"));
			database.getTable(rs.getString("TBLNAME")).
				addColumn(column);
		}
		
		//keys
		rs = connection.prepareStatement("SELECT * FROM KEE WHERE DBNAME = '"+database.getName()+"'").executeQuery();
		while(rs.next()) {
			Key key = new Key(rs.getString("NAME"));
			database.addKey(key);
			database.getTable(rs.getString("TBLNAME")).
				addKey(key);
		}
		
		//key columns
		rs = connection.prepareStatement("SELECT * FROM KEECOL WHERE DBNAME = '"+database.getName()+"'").executeQuery();
		while(rs.next()) {
			Key key = database.getKey(rs.getString("KEENAME"));
			Table table = database.getTable(rs.getString("TBLNAME"));
			Column column = table.getColumn(rs.getString("COLNAME"));
			key.setColumnAt(column, rs.getInt("SEQNR"));
		}
		
		//primary keys
		rs = connection.prepareStatement("SELECT * FROM PKEE WHERE DBNAME = '"+database.getName()+"'").executeQuery();
		while(rs.next()) {
			Table table = database.getTable(rs.getString("TBLNAME"));
			Key key = table.getKey(rs.getString("KEENAME"));
			table.setPrimaryKey(key);
		}
		
		//foreign keys
		rs = connection.prepareStatement("SELECT * FROM FKEE WHERE DBNAME = '"+database.getName()+"'").executeQuery();
		while(rs.next()) {
			Table table = database.getTable(rs.getString("TBLNAME"));
			ForeignKey foreignKey = new ForeignKey(rs.getString("NAME"), database.getKey(rs.getString("TOKEENAME")));
			database.addForeignKey(foreignKey);
			table.addForeignKey(foreignKey);
		}
		
		//foreign key columns
		rs = connection.prepareStatement("SELECT * FROM FKEECOL WHERE DBNAME = '"+database.getName()+"'").executeQuery();
		while(rs.next()) {
			ForeignKey foreignKey = database.getForeignKey(rs.getString("FKEENAME"));
			Table table = database.getTable(rs.getString("TBLNAME"));
			Column column = table.getColumn(rs.getString("COLNAME"));
			foreignKey.setColumnAt(column, rs.getInt("SEQNR"));
		}
	}
	
	/**
	 * Extract a database structure from a database via a java.sql.Connection object. <br>
	 * <br>
	 * This method should only be used for academic purposes.<br>
	 *  
	 * @param connection The connection from which to retrieve the database structure.
	 * @throws SQLException Thrown when retrieving data from the given database connection creates an Exception.
	 * @throws DDLException Thrown when there is an inconsistency present in the stored database structure, prevented by the RDBMS' database integrity checks.
	 */
	public void loadAcademicDatabase(Connection connection) throws SQLException, DDLException {
		//tables & columns
		ResultSet rsTables = connection.prepareStatement(
				"SELECT * FROM TBL WHERE dbname = '"+database.getName()+"'").executeQuery();
		while(rsTables.next()) {
			String tableName = rsTables.getString("name");
			Table table = new Table(tableName);
			
			ResultSet rsColumns = connection.prepareStatement(
					"SELECT * FROM COL WHERE dbname = '"+database.getName()+"' AND tblname = '"+tableName+"'").executeQuery();
			while(rsColumns.next()) {
				Column column = new Column(
						rsColumns.getString("name"),
						rsColumns.getString("datatype"),
						rsColumns.getBoolean("notnull"));
				table.addColumn(column);
				column.init();
			}
			rsColumns.close();
			
			database.addTable(table);
		}
		rsTables.close();
		
		
		//setup namespace
		String identifyingColumnName 	= null;
		String dbReferenceColumnName 	= null;
		String tblReferenceColumnName 	= null;
		String keeReferenceColumnName 	= null;
		String colReferenceColumnName 	= null;
		String seqnrColumnName 			= null;
		String keePointerColumnName 	= null;
		String fKeeReferenceColumnName 	= null;
		
		String keeTableName 			= null;
		String keeColTableName 			= null;
		String pKeeTableName 			= null;
		String fKeeTableName 			= null;
		String fKeeColTableName 		= null;
		
		
		//deriving relevance for all tables and structually related columns
		
		//db
		for(String tableName : database.getAllTableNames()) {
			Table table = database.getTable(tableName);
			Set<String> columnNames = table.getAllColumnNames();
			
			if(columnNames.size() == 1) {
				identifyingColumnName = columnNames.iterator().next();
			}
		}
		
		//tbl
		for(String tableName : database.getAllTableNames()) {
			Table table = database.getTable(tableName);
			Set<String> columnNames = table.getAllColumnNames();
			
			if(columnNames.size() == 2) {
				for(String columnName : columnNames) {
					if(!columnName.equals(identifyingColumnName)) {
						dbReferenceColumnName = columnName;
					}
				}
			}
		}
	
		//kee
		for(String tableName : database.getAllTableNames()) {
			Table table = database.getTable(tableName);
			Set<String> columnNames = table.getAllColumnNames();
			
			if(columnNames.size() == 3 
					&& columnNames.contains(identifyingColumnName))  {
				keeTableName = tableName;
				
				for(String columnName : columnNames) {
					if(!columnName.equals(identifyingColumnName)
							&& !columnName.equals(dbReferenceColumnName)) {
						tblReferenceColumnName = columnName;
					}
				}
			}
		}

		//pkee
		for(String tableName : database.getAllTableNames()) {
			Table table = database.getTable(tableName);
			Set<String> columnNames = table.getAllColumnNames();
			
			if(columnNames.size() == 3 
					&& !columnNames.contains(identifyingColumnName)) {
				pKeeTableName = tableName;

				for(String columnName : columnNames) {
					if(!columnName.equals(dbReferenceColumnName)
							&& !columnName.equals(tblReferenceColumnName)) {
						keeReferenceColumnName = columnName;
					}
				}
			}
		}
		
		//keecol
		for(String tableName : database.getAllTableNames()) {
			Table table = database.getTable(tableName);
			Set<String> columnNames = table.getAllColumnNames();
			
			if(columnNames.size() == 5 
				&& columnNames.contains(keeReferenceColumnName)) {
				keeColTableName = tableName;

				for(String columnName : columnNames) {
					if(!columnName.equals(dbReferenceColumnName)
							&& !columnName.equals(tblReferenceColumnName)
							&& !columnName.equals(keeReferenceColumnName)) {
						Column column = table.getColumn(columnName);
						
						if(column.getDatatype().equals(SQL.DATATYPE_STRING))
							colReferenceColumnName = columnName;
						else if(column.getDatatype().equals(SQL.DATATYPE_INT))
							seqnrColumnName = columnName;
					}
				}
			}
		}
		
		//fkee
		for(String tableName : database.getAllTableNames()) {
			Table table = database.getTable(tableName);
			Set<String> columnNames = table.getAllColumnNames();
			
			if(columnNames.size() == 4) {
				fKeeTableName = tableName;
				
				for(String columnName : columnNames) {
					if(!columnName.equals(identifyingColumnName)
							&& !columnName.equals(dbReferenceColumnName)
							&& !columnName.equals(tblReferenceColumnName)) {
						keePointerColumnName = columnName;
					}
				}
			}
		}
		
		//fkeecol
		for(String tableName : database.getAllTableNames()) {
			Table table = database.getTable(tableName);
			Set<String> columnNames = table.getAllColumnNames();
			
			if(columnNames.size() == 5
					&& !columnNames.contains(identifyingColumnName)) {
				fKeeColTableName = tableName;

				for(String columnName : columnNames) {
					if(!columnName.equals(dbReferenceColumnName)
							&& !columnName.equals(tblReferenceColumnName)
							&& !columnName.equals(colReferenceColumnName)
							&& !columnName.equals(seqnrColumnName)) {
						fKeeReferenceColumnName = columnName;
					}
				}
			}
		}
		
		
		//Retrieving the remaining metadata and setting up the in-core database
		
		//keys
		ResultSet rsKeys = connection.prepareStatement(
				"SELECT * FROM "+keeTableName+" "+
				"WHERE "+dbReferenceColumnName+" = '"+database.getName()+"'").executeQuery();
		while(rsKeys.next()) {
			Key key = new Key(rsKeys.getString(identifyingColumnName));
			Table table = database.getTable(rsKeys.getString(tblReferenceColumnName));
			
			//key columns
			ResultSet rsKeyColumns = connection.prepareStatement(
					"SELECT * FROM "+keeColTableName+" "+
					"WHERE "+dbReferenceColumnName+" = '"+database.getName()+"' "+
					"AND "+keeReferenceColumnName+" = '"+key.getName()+"'").executeQuery();
			while(rsKeyColumns.next()) {
				key.setColumnAt(
						table.getColumn(rsKeyColumns.getString(colReferenceColumnName)),
						rsKeyColumns.getInt(seqnrColumnName));
			}
			rsKeyColumns.close();
			
			table.addKey(key);
			database.addKey(key);
			key.init();
		}
		rsKeys.close();

		//primary keys
		ResultSet rsPrimaryKeys = connection.prepareStatement(
				"SELECT * FROM "+pKeeTableName+" "+
				"WHERE "+dbReferenceColumnName+" = '"+database.getName()+"'").executeQuery();
		while(rsPrimaryKeys.next()) {
			Table table = database.getTable(rsPrimaryKeys.getString(tblReferenceColumnName));
			table.setPrimaryKey(table.getKey(rsPrimaryKeys.getString(keeReferenceColumnName)));
		}
		rsPrimaryKeys.close();
		
		//foreign keys
		ResultSet rsForeignKeys = connection.prepareStatement(
				"SELECT * FROM "+fKeeTableName+" "+
				"WHERE "+dbReferenceColumnName+" = '"+database.getName()+"'").executeQuery();
		while(rsForeignKeys.next()) {
			ForeignKey foreignKey = new ForeignKey(rsForeignKeys.getString(identifyingColumnName),
					database.getKey(rsForeignKeys.getString(keePointerColumnName)));
			Table table = database.getTable(rsForeignKeys.getString(tblReferenceColumnName));
			
			//key columns
			ResultSet rsForeignKeyColumns = connection.prepareStatement(
					"SELECT * FROM "+fKeeColTableName+" "+
					"WHERE "+dbReferenceColumnName+" = '"+database.getName()+"' "+
					"AND "+fKeeReferenceColumnName+" = '"+foreignKey.getName()+"'").executeQuery();
			while(rsForeignKeyColumns.next()) {
				foreignKey.setColumnAt(
						table.getColumn(rsForeignKeyColumns.getString(colReferenceColumnName)),
						rsForeignKeyColumns.getInt(seqnrColumnName));
			}
			rsForeignKeyColumns.close();
			
			table.addForeignKey(foreignKey);
			database.addForeignKey(foreignKey);
			foreignKey.init();
		}
		rsForeignKeys.close();
	}
	
	/**
	 * Extract a database structure from a database via a java.sql.Connection object. <br>
	 * <br>
	 * This method should only be used for academic purposes.<br>
	 *   
	 * @param connection The connection from which to retrieve the database structure.
	 * @throws SQLException Thrown when retrieving data from the given database connection creates an Exception.
	 * @throws DDLException Thrown when there is an inconsistency present in the stored database structure, prevented by the RDBMS' database integrity checks.
	 */
	public void fillAcademicDatabase(Connection connection) throws SQLException, DMLException {
		
		//database
		ResultSet rsDatabase = connection.prepareStatement(
			"SELECT * FROM DB WHERE name = '"+database.getName()+"'").executeQuery();
		if(rsDatabase.next()) {
			String databaseName = rsDatabase.getString("name");
			
			insert(
					databaseName, 
					new String[]{"name"}, 
					new String[][]{{databaseName}}
					);
			
		} else {
			throw new SQLException("Database not found: "+database.getName());
		}	
		rsDatabase.close();
		
		
		//tables & columns
		ResultSet rsTables = connection.prepareStatement(
				"SELECT * FROM TBL WHERE dbname = '"+database.getName()+"'").executeQuery();
		while(rsTables.next()) {
			String tableName = rsTables.getString("name");
			
			insert(
					"TBL", 
					new String[]{"dbname","name"}, 
					new String[][]{{database.getName(), tableName}}
					);
			
			//columns
			List<String[]> tableContents = new ArrayList<String[]>(); 
			
			ResultSet rsColumns = connection.prepareStatement(
					"SELECT * FROM COL WHERE dbname = '"+database.getName()+"' AND tblname = '"+tableName+"'").executeQuery();
			while(rsColumns.next()) {
				String[] columnRow = new String[5];
				columnRow[0] = rsColumns.getString("dbname");
				columnRow[1] = rsColumns.getString("tblname");
				columnRow[2] = rsColumns.getString("name");
				columnRow[3] = rsColumns.getString("datatype");
				columnRow[4] = rsColumns.getString("notnull");
				tableContents.add(columnRow);
			}
			rsColumns.close();
			
			String[][] stringMatrix = tableContents.toArray(new String[0][0]);
			
			insert(
					"COL",
					new String[]{"dbname", "tblname", "name", "datatype", "notnull"},
					stringMatrix
					);
		}
		rsTables.close();
		
		
		//Other metadata
		//Set<String> tableNames = database.getAllTableNames(); //TOO RANDOM, for isntance: can't insert KEECOL when KEE doesnt exist yet
		String[] tableNames = new String[]{"KEE","KEECOL","PKEE","FKEE","FKEECOL"}; 
		
		for(String tableName : tableNames) {
			
			//var setup
			List<String[]> tableContents = new ArrayList<String[]>();
			Table table = database.getTable(tableName);
			String[] columnNames = table.getAllColumnNames().toArray(new String[0]);
			
			//get the data
			ResultSet rsTableContents = connection.prepareStatement(
					"SELECT * FROM "+tableName+" WHERE dbname = '"+database.getName()+"'").executeQuery();
			while(rsTableContents.next()) {
				String[] row = new String[columnNames.length];
				for(int i = 0; i < columnNames.length; ++i) {
					row[i] = rsTableContents.getString(columnNames[i]);
				}
				tableContents.add(row);
			}
			rsTableContents.close();
			
			String[][] stringMatrix = tableContents.toArray(new String[0][0]);
			
			insert(
					tableName,
					columnNames,
					stringMatrix
					);
		}
	}

	/**
	 * Saves the database structure to a storage database via a java.sql.Connection object. <br>
	 * 
	 * @param connection The connection to use to save this database.
	 * @throws SQLException Thrown when any interaction with the given database connection creates an Exception.
	 */
	public void saveDatabase(Connection connection) throws SQLException {
		
		//first save the database with a temporary name, current name might already be in use
		String tempDBName = database.getName()+TEMP_SUFFIX_DATABASENAME;

		//Make sure the database exists in the database provided by the connection, before making any other alterations
		removeDatabaseByName(connection, tempDBName);
		saveDatabaseByName(connection, tempDBName);
		
		//remove potential existing older version of this database
		removeDatabaseByName(connection, database.getName());
		
		//Save the database under the real name
		saveDatabaseByName(connection, database.getName());
		
		//remove the temporary database save
		removeDatabaseByName(connection, tempDBName);
	}
	
	private void saveDatabaseByName(Connection connection, String dbName) throws SQLException {
		//database
		connection.prepareStatement(
				"INSERT INTO DB(name)" +
				"VALUES" +
				"('"+dbName+"')" +
			";").execute();
		
		//tables
		for(String tableName : database.getAllTableNames()) {
			Table table = database.getTable(tableName);
			
			connection.prepareStatement(
					"INSERT INTO TBL(dbname, name)" +
						"VALUES" +
						"('"+dbName+"',	'"+tableName+"')" +
					";").execute();
			
			//columns
			for(String columnName : table.getAllColumnNames()) {
				Column column = table.getColumn(columnName);
				
				connection.prepareStatement(
						"INSERT INTO COL(dbname, tblname, name, datatype, notnull)" +
							"VALUES" +
							"('"+dbName+"', '"+tableName+"', '"+columnName+"', '"+column.getDatatype()+"', "+column.getNotnull()+")" +
						";").execute();
			}
			
			//keys
			for(String keyName : table.getAllKeyNames()) {
				Key key = table.getKey(keyName);
				
				connection.prepareStatement(
						"INSERT INTO KEE(dbname, tblname, name)" +
							"VALUES" +
							"('"+dbName+"', '"+tableName+"', '"+keyName+"')" +
						";").execute();
				
				//key columns
				Column[] keyColumns = key.getColumnsOrdered();
				for(int i = 0; i < keyColumns.length; ++i) {
					connection.prepareStatement(
							"INSERT INTO KEECOL(dbname, tblname, colname, keename, seqnr)" +
								"VALUES" +
								"('"+dbName+"', '"+tableName+"', '"+keyColumns[i].getName()+"', '"+keyName+"', "+i+")" +
							";").execute();
				}
			}
			
			connection.prepareStatement(
					"INSERT INTO PKEE(dbname, tblname, keename)" +
						"VALUES" +
						"('"+dbName+"', '"+tableName+"', '"+table.getPrimaryKey().getName()+"')" +
					";").execute();
		}
		
		//table container has random order, therefore foreign keys can only be stored after all previous data is already present
		for(String tableName : database.getAllTableNames()) {
			Table table = database.getTable(tableName);
			
			//foreign keys
			for(String foreignKeyName : table.getAllForeignKeyNames()) {
				ForeignKey foreignKey = table.getForeignKey(foreignKeyName);
				
				connection.prepareStatement(
						"INSERT INTO FKEE(dbname, tblname, name, tokeename)" +
							"VALUES" +
							"('"+dbName+"', '"+tableName+"', '"+foreignKey.getName()+"', '"+foreignKey.getToKey().getName()+"')" +
						";").execute();
				
				//key columns
				Column[] foreignKeyColumns = foreignKey.getColumnsOrdered();
				for(int i = 0; i < foreignKeyColumns.length; ++i) {
					connection.prepareStatement(
							"INSERT INTO FKEECOL(dbname, tblname, colname, fkeename, seqnr)" +
								"VALUES" +
								"('"+dbName+"', '"+tableName+"', '"+foreignKeyColumns[i].getName()+"', '"+foreignKeyName+"', "+i+")" +
							";").execute();
				}
			}
		}
	}
	
	private void removeDatabaseByName(Connection connection, String dbName) throws SQLException {
		String[] reverseTableNames = new String[]{"FKEECOL","FKEE","PKEE","KEECOL","KEE", "COL", "TBL"};
		for(String tableName : reverseTableNames) {
			connection.prepareStatement(
					"DELETE FROM " + tableName + " " +
						"WHERE dbname = '"+dbName+"'" +
					";").execute();
		}
		connection.prepareStatement(
				"DELETE FROM DB " +
					"WHERE name = '"+dbName+"'" +
				";").execute();
	}
	
	//Public util methods
	/**
	 * Prints out the entire structure of the database (or metadata if you will) into the console. <br>
	 */
	public void printAllMetadata() {
		
		System.out.println("Database "+database.getName()+":");
		
		for(String tableName : database.getAllTableNames()) {
			
			Table table = database.getTable(tableName);
			System.out.println("\tTable "+tableName+":");
			for(String columnName : table.getAllColumnNames()) {
				Column column = table.getColumn(columnName);
				System.out.println("\t\t"+columnName+" "+column.getDatatype()+" "+(column.getNotnull()?"NOTNULL":""));
			}
			
			Key pkey = table.getPrimaryKey();
			if(pkey != null)
				System.out.println("\t\tPrimary Key: "+pkey.getName());
			
			System.out.println("\t\tKeys:");
			for(String keyName : table.getAllKeyNames()) {
				Key key = table.getKey(keyName);
				System.out.print("\t\t\t"+keyName+": ");
				for(Column column : key.getColumnsOrdered()) {
					System.out.print(column.getName()+", ");
				}
				System.out.println();
			}
			
			System.out.println("\t\tForeign Keys:");
			for(String foreignKeyName : table.getAllForeignKeyNames()) {
				ForeignKey foreignKey = table.getForeignKey(foreignKeyName);
				System.out.print("\t\t\t"+foreignKeyName+" (references "+foreignKey.getToKey().getName()+"): ");
				for(Column column : foreignKey.getColumnsOrdered()) {
					System.out.print(column.getName()+", ");
				}
				System.out.println();
			}
			
		}
	}

	/**
	 * Prints out the entire database contents into the console. <br>
	 */
	public void printAllData() {
		for(String tableName : database.getAllTableNames()) {
			System.out.println("Table: "+tableName);
			Table table = database.getTable(tableName);
			Set<String> columnNames = table.getAllColumnNames();

			for(String columnName : columnNames) {
				System.out.print("["+columnName+"], \t");
			}
			System.out.println("");
			
			for(int i = 0; i < table.getColumn(table.getAllColumnNames().iterator().next()).getRowCount(); ++i) {
				for(String columnName : columnNames) {
					Column column = table.getColumn(columnName);
					System.out.print(column.getValue(i)+", \t\t");
				}
				System.out.println("");
			}
			System.out.println("");
		}
	}
	
	/**
	 * Prints out the entire database indexing contents into the console. <br>
	 */
	public void printAllIndexes() {
		for(String keyName : database.getAllKeyNames()) {
			Key key = database.getKey(keyName);
			System.out.println(key.getName()+":");
			key.printIndex();
			System.out.println("");
		}
	}
	
	//DDL (Data Definition Language)
	
	//private methods for error checks
	private Table getTableCheckIfExists(String tableName) throws DDLException {
		Table table = database.getTable(tableName);
		
		if(!database.hasTable(tableName))
			throw new NonExistingEntryException("Table name \""+tableName+"\" does not exist");
		
		if(table == null)
			throw new DatabaseIntegrityException("Table name \""+tableName+"\" exists but table object doesn't");
		
		return table;
	}
	
	private Key getKeyCheckIfExists(String keyName) throws DDLException {
		Key key = database.getKey(keyName);
		
		if(!database.hasKey(keyName))
			throw new NonExistingEntryException("Key name \""+keyName+"\" does not exist");
		
		if(key == null)
			throw new DatabaseIntegrityException("Key name \""+keyName+"\" exists but target key object doesn't");
		
		return key;
	}
	
	private ForeignKey getForeignKeyCheckIfExists(String foreignKeyName) throws DDLException {
		ForeignKey foreignKey = database.getForeignKey(foreignKeyName);
		
		if(!database.hasForeignKey(foreignKeyName))
			throw new NonExistingEntryException("Foreign Key name \""+foreignKeyName+"\" does not exist");
		
		if(foreignKey == null)
			throw new DatabaseIntegrityException("Foreign Key name \""+foreignKeyName+"\" exists but target key object doesn't");
		
		return foreignKey;
	}
	
	private Column getColumnCheckIfExists(Table table, String columnName) throws DDLException {
		Column column = table.getColumn(columnName);
		
		if(!table.hasColumn(columnName))
			throw new NonExistingEntryException("Column name \""+columnName+"\" does not exist in table \""+table.getName()+"\"");
		
		if(column == null)
			throw new DatabaseIntegrityException("Column name \""+columnName+"\" exists but column object doesn't");
		
		return column;
	}
	
	private void checkIfKeyExistsInTable(Table table, String keyName) throws DDLException {
		if(!table.hasKey(keyName))
			throw new NonExistingEntryException("Key name \""+keyName+"\" does not exist in table \""+table.getName()+"\"");
	}
	
	private void checkIfForeignKeyExistsInTable(Table table, String foreignKeyName) throws DDLException {
		if(!table.hasForeignKey(foreignKeyName))
			throw new NonExistingEntryException("Foreign Key name \""+foreignKeyName+"\" does not exist in table \""+table.getName()+"\"");
	}
	
	private void checkIfNameValid(String name, String objectClassName, String pattern) throws DDLException {
		if(name.length() == 0)
			throw new NameNotValidException(objectClassName+" name can not be empty");
		
		if(!Character.isLetter(name.charAt(0)))
			throw new NameNotValidException(objectClassName+" name \""+name+"\" must start with a letter");
		
		if(Pattern.compile(pattern).matcher(name).find())
			throw new NameNotValidException(objectClassName+" name \""+name+"\" contains a character that matches \""+pattern+"\"");
	}
	
	private void checkIfObjectNotAlreadyExists(String objectName, String objectClassName) throws DDLException {
		switch (objectClassName) {
			case ("Table") :
				if(database.hasTable(objectName))
					throw new DuplicateEntryException(objectClassName+" name \""+objectName+"\" already exists");
				break;
			case ("Key") :
				if(database.hasKey(objectName))
					throw new DuplicateEntryException(objectClassName+" name \""+objectName+"\" already exists");
				break;
			case ("Foreign Key") :
				if(database.hasForeignKey(objectName))
					throw new DuplicateEntryException(objectClassName+" name \""+objectName+"\" already exists");
				break;
		}
	}
	
	private void checkIfObjectNotAlreadyExistsInTable(Table table, String objectName, String objectClassName) throws DDLException {
		switch (objectName) {
			case ("Column") :
				if(table.hasColumn(objectName))
					throw new DuplicateEntryException(objectClassName+" name \""+objectName+"\" already exists");
				break;
			case ("Key") :
				if(table.hasKey(objectName))
					throw new DuplicateEntryException(objectClassName+" name \""+objectName+"\" already exists");
				break;
			case ("Foreign Key") :
				if(table.hasForeignKey(objectName))
					throw new DuplicateEntryException(objectClassName+" name \""+objectName+"\" already exists");
				break;
		}
	}
	
	private void checkIfColumnCountNotZero(String[] columnNames, String containerObjectName, String containerObjectClassName) throws DDLException {
		if(columnNames.length == 0)
			throw new InvalidColumnSetException("At least one column must be specified for "+containerObjectClassName+" \""+containerObjectName+"\"");
	}
	
	private void checkIfColumnArrayNotContainsDuplicates(String[] columnNames, String containerObjectName, String containerObjectClassName) throws DDLException {
		if(new HashSet<String>(Arrays.asList(columnNames)).size() < columnNames.length)
			throw new DuplicateEntryException("Columns can't be in same "+containerObjectClassName+" \""+containerObjectName+"\" more than once");
	}
	
	private void checkIfKeyNotAlreadyExistsInTableByColDef(Table table, Key key) throws DDLException {
		for(String potentialDuplicateKey : table.getAllKeyNames()) {
			if(Arrays.equals(key.getColumnsOrdered(), table.getKey(potentialDuplicateKey).getColumnsOrdered()))
				throw new DuplicateEntryException("Key \""+key.getName()+"\" already exists in table \""+table.getName()+"\" with name \""+potentialDuplicateKey+"\"");
		}
	}

	private void checkIfColumnDatatypeIsValid(String datatype, String columnName) throws DDLException {
		switch (datatype) {
			case SQL.DATATYPE_BOOLEAN:
			case SQL.DATATYPE_INT:
			case SQL.DATATYPE_STRING:
				break;
			default:
				throw new InvalidColumnDatatypeException("Datatype \""+datatype+"\" for column \""+columnName+"\" invalid");
		}
	}
	
	private void checkIfColumnSetHasEqualLengths(String[] columnNames, String[] datatypes, Boolean[] notnulls, String tableName) throws DDLException {
		if(columnNames.length != datatypes.length || datatypes.length != notnulls.length)
			throw new InvalidColumnSetException("All arrays containing column attributes must be of equal length for table \""+tableName+"\"");
	}

	private void checkIfTargetKeyNotFromTable(Table table, String targetKeyName) throws DDLException {
		if(table.hasKey(targetKeyName))
			throw new DatabaseIntegrityException("Table \""+table.getName()+"\" can't be same as that of Target Key \""+targetKeyName+"\"");
	}
	
	private void checkIfTargetKeyColumnCountMatches(String[] columnNames, Column[] targetColumnNames, String foreignKeyName, String targetKeyName) throws DDLException {
		if(columnNames.length != targetColumnNames.length)
			throw new InvalidColumnSetException("Column count ("+columnNames.length+") of foreign key \""+foreignKeyName+"\"" +
					"does not match column count ("+targetColumnNames.length+") of target key \""+targetKeyName+"\"");
	}
	
	private void checkIfColumnDatatypesMatch(Column column, Column targetKeyColumn, String targetKey ) throws DDLException {
		if(!column.getDatatype().equals(targetKeyColumn.getDatatype()))
			throw new InvalidColumnSetException("Datatype ("+column.getDatatype()+") of column \""+column.getName()+"\" " +
					"does not match datatype ("+targetKeyColumn.getDatatype()+") of target key \""+targetKey+"\" column \""+targetKeyColumn.getName()+"\"");
	}
	
	private void checkIfTableHasNoPrimaryKey(Table table) throws DDLException {
		Key existingPrimaryKey = table.getPrimaryKey();
		if(existingPrimaryKey != null)
			throw new PrimaryKeyException("Table \""+table.getName()+"\" already has a primary key. Drop \""+existingPrimaryKey.getName()+"\" first");
	}
	
	private void checkIfTableHasTargetPrimaryKey(Table table, String keyName) throws DDLException {
		if(!table.hasKey(keyName))
			throw new PrimaryKeyException("Target Primary Key \""+keyName+"\" is not part of table \""+table.getName()+"\"");
	}
	
	private void checkIfRenameNotIneffective(String oldName, String newName, String objectClassName) throws DDLException {
		if(oldName.equals(newName))
			throw new IneffectiveAlterationException(objectClassName+" name \""+oldName+"\" equals the new name");
	}
	
	private void checkIfForeignKeyNotReferringTable(Table table, String foreignKeyName) throws DDLException {
		if(!table.hasForeignKey(foreignKeyName)) {	//Even if there are foreign keys from this table pointing to this table's keys, the table should still be droppable
			ForeignKey foreignKey = database.getForeignKey(foreignKeyName);
			String toKeyName = foreignKey.getToKey().getName();
			if(table.getAllKeyNames().contains(toKeyName))
					throw new ForeignKeyRestrictionException("Foreign key \""+foreignKeyName+"\" points to key \""+toKeyName+"\" of table \""+table.getName()+"\"");
		}
	}

	private void checkIfForeignKeyNotReferringKey(Key key, String foreignKeyName) throws DDLException {
		ForeignKey foreignKey = database.getForeignKey(foreignKeyName);
		if(key.getName().equals(foreignKey.getToKey().getName())) 
			throw new ForeignKeyRestrictionException("Foreign Key \""+foreignKey.getName()+"\" points to \""+key.getName()+"\", drop it first");
	}
	
	private void checkIfTableHasAPrimaryKey(Table table) throws DDLException {
		if(table.getPrimaryKey() == null)
			throw new NonExistingEntryException("Table \""+table.getName()+"\" does not have a primary key");
	}

	
	/******************\
	|* CREATE METHODS *| 
	\******************/
	
	/**
	 * Adds a new Table object to the database. <br>
	 * <br>
	 * The name of the table must be alphanumeric and unique per database.<br>
	 * The name of each column must be alphanumeric and unique.<br>
	 * <br>
	 * Every Column object has a datatype specification, the data that will enter this column must be of that type. <br>
	 * The possible datatypes values and their Java counterparts (java.lang) are:<br>
	 * <ul>
	 * <li>"int" - Integer</li>
	 * <li>"str" - String</li>
	 * <li>"bln" - Boolean</li>
	 * </ul>
	 * Use the public static final DATATYPE vars found in metadata.Column.<br>
	 * <br>
	 * A Column object has the attribute "notnull". The default is false.<br>
	 * Set this to true if the data that will column will contain is not allowed to be a null value.<br>
	 * 
	 * @param tableName The name of the new table. 
	 * @param columnNames The names of the new columns.
	 * @param datatypes The datatypes of the data that the new columns will contain.
	 * @param notnulls The specification on whether the data is allowed to be a null value or not for each column.
	 * @throws DDLException TODO
	 * @see metadata.Table
	 * @see metadata.Column
	 */
	public void createTable(
			String tableName, 
			String[] columnNames, 
			String[] datatypes,
			Boolean[] notnulls) throws DDLException {
		
		checkIfObjectNotAlreadyExists(tableName, "Table");
		checkIfNameValid(tableName, "Table", PATTERN_DISSALOWED_CHARS_FOR_NAMES);
		checkIfColumnCountNotZero(columnNames, tableName, "Table");
		checkIfColumnSetHasEqualLengths(columnNames, datatypes, notnulls, tableName);
		checkIfColumnArrayNotContainsDuplicates(columnNames, tableName, "Table");
		
		Table table = new Table(tableName);
		for(int i = 0; i < columnNames.length; ++i) {
			checkIfNameValid(columnNames[i], "Column", PATTERN_DISSALOWED_CHARS_FOR_NAMES);
			checkIfColumnDatatypeIsValid(datatypes[i], columnNames[i]);
			
			Column column = new Column(columnNames[i], datatypes[i], notnulls[i]);
			table.addColumn(column);
			column.init();
		}
		
		database.addTable(table);
	}
	
	/**
	 * Adds a new Key object to the database and an already existing Table object. <br>
	 * <br>
	 * The name of the key must be alphanumeric and unique per database.<br>
	 * The order of the columns in the given array of columnNames will be the order remembered by the key.<br>
	 * A key can not be created if the same set of columns in the same order already exist in another key in the same table.<br>
	 * 
	 * @param keyName The name of the new key.
	 * @param tableName The name of the existing table that will contain the new key.
	 * @param columnNames The names of the existing columns that the new key will span across.
	 * @throws DDLException TODO
	 * @see metadata.Database
	 * @see metadata.Table
	 * @see metadata.Column
	 * @see metadata.Key
	 */
	public void createKey(
			String keyName, 
			String tableName, 
			String[] columnNames) throws DDLException {
		
		Table table = getTableCheckIfExists(tableName);
		
		checkIfObjectNotAlreadyExists(keyName, "Key");
		checkIfNameValid(keyName, "Key", PATTERN_DISSALOWED_CHARS_FOR_NAMES);
		checkIfColumnCountNotZero(columnNames, keyName, "Key");
		checkIfColumnArrayNotContainsDuplicates(columnNames, keyName, "Key");
		
		Key key = new Key(keyName);
		for(int i = 0; i < columnNames.length; ++i) {
			Column column = getColumnCheckIfExists(table, columnNames[i]);

			key.setColumnAt(column, i);
		}
		checkIfKeyNotAlreadyExistsInTableByColDef(table, key);
		
		database.addKey(key);
		table.addKey(key);
		key.init();
	}
	
	/**
	 * Adds a new Foreign Key object to the database and an already existing Table object. <br>
	 * <br>
	 * The name of the foriegn key must be alphanumeric and unique per database.<br>
	 * The order of the columns in the given array of columnNames will be the order remembered by the foreign key.<br>
	 * <br>
	 * Every Foreign Key object points to an already existing key.<br>
	 * The amount of columns the foreign key spans must match that of the key that the foreign key points to.<br>
	 * Not only that, but the datatype of each column must match respectively as well.<br>
	 * A foreign key can not be in the same table as the key it points to.<br>
	 * 
	 * @param foreignKeyName The name of the new foreign key.
	 * @param tableName The name of the existing table that will contain the new foreign key.
	 * @param columnNames The names of the existing columns that the new foreign key will span across.
	 * @param toKeyName The name of the existing key to which the new foreign key connects.
	 * @throws DDLException TODO
	 * @see metadata.Database
	 * @see metadata.ForeignKey
	 * @see metadata.Key
	 */	
	public void createForeignKey(
			String foreignKeyName, 
			String tableName, 
			String[] columnNames,
			String toKeyName) throws DDLException {
		
		Table table = getTableCheckIfExists(tableName);
		Key toKey = getKeyCheckIfExists(toKeyName);
		
		checkIfTargetKeyNotFromTable(table, toKeyName);
		checkIfObjectNotAlreadyExists(foreignKeyName, "Foreign Key");
		checkIfNameValid(foreignKeyName, "Foreign Key", PATTERN_DISSALOWED_CHARS_FOR_NAMES);
		checkIfColumnCountNotZero(columnNames, foreignKeyName, "Foreign Key");
		checkIfColumnArrayNotContainsDuplicates(columnNames, foreignKeyName ,"Foreign Key");
		
		Column[] targetKeyColumns = toKey.getColumnsOrdered();
		checkIfTargetKeyColumnCountMatches(columnNames, targetKeyColumns, foreignKeyName, toKeyName);
		
		ForeignKey foreignKey = new ForeignKey(foreignKeyName, toKey);
		for(int i = 0; i < columnNames.length; ++i) {
			Column column = getColumnCheckIfExists(table, columnNames[i]);
			
			checkIfColumnDatatypesMatch(column, targetKeyColumns[i], toKeyName);
			
			foreignKey.setColumnAt(column, i);
		}
		
		database.addForeignKey(foreignKey);
		table.addForeignKey(foreignKey);
		foreignKey.init();
	}
		
	/**
	 * Appoints an existing key to become an existing table's Primary Key. <br>
	 * <br>
	 * A table may have only one primary key. Remove a existing primary key before appointing a new one.<br>
	 * <br>
	 * A table object has references to each key that spans across columns of that table object.<br>
	 * The key that is chosen to become a primary key must therefore be referenced to by the table that is chosen.<br>
	 * Any other scenario should result in an error.<br>
	 * 
	 * @param keyName The name of the existing key to become a primary key.
	 * @param tableName The name of the existing table.
	 * @throws DDLException TODO
	 * @see metadata.Table
	 * @see metadata.Key
	 */	
	public void createPrimaryKey(
			String keyName, 
			String tableName) throws DDLException {
		
		Table table = getTableCheckIfExists(tableName);
		Key key = getKeyCheckIfExists(keyName);
		
		checkIfTableHasNoPrimaryKey(table);
		checkIfTableHasTargetPrimaryKey(table, keyName);
		
		table.setPrimaryKey(key);
	}
	
	/**
	 * Adds a new Key object to the database and an already existing Table object.<br>
	 * Appoints this new key to become the table's Primary Key. <br>
	 * <br>
	 * This method should do the same as calling createKey(primaryKeyName, tableName, columnNames)<br>
	 * and then createPrimaryKey(primaryKeyName, tableName), but without the overhead of double integrity checks.<br>
	 * <br>
	 * This is also the preferred method to use when creating the primary key during table creation,<br>
	 * since calling createKey() puts objects in the database even though createPrimaryKey() could still case errors.<br>
	 * <br>
	 * Despite suggesting to use this during table creation, because of the structure of RDBMS procedures,<br>
	 * to guarantee database integrity, there will be checked if the table already has a primary key assigned.<br>
	 * 
	 * @param primaryKeyName The name of the new key.
	 * @param tableName The name of the existing table that will contain the new key.
	 * @param columnNames The names of the existing columns that the new key will span across.
	 * @throws DDLException TODO
	 * @see metadata.Table
	 * @see metadata.Key
	 */
	public void createPrimaryKey(
			String primaryKeyName, 
			String tableName, 
			String[] columnNames) throws DDLException {
		
		Table table = getTableCheckIfExists(tableName);
		
		checkIfTableHasNoPrimaryKey(table);
		checkIfObjectNotAlreadyExists(primaryKeyName, "Key");
		checkIfNameValid(primaryKeyName, "Key", PATTERN_DISSALOWED_CHARS_FOR_NAMES);
		checkIfColumnCountNotZero(columnNames, primaryKeyName, "Key");
		checkIfColumnArrayNotContainsDuplicates(columnNames, primaryKeyName, "Key");
		
		Key key = new Key(primaryKeyName);
		for(int i = 0; i < columnNames.length; ++i) {
			Column column = getColumnCheckIfExists(table, columnNames[i]);

			key.setColumnAt(column, i);
		}
		checkIfKeyNotAlreadyExistsInTableByColDef(table, key);
		
		database.addKey(key);
		table.addKey(key);
		table.setPrimaryKey(key);
		key.init();
	}

	/*****************\
	|* ALTER METHODS *| 
	\*****************/
	
	/**
	 * Renames an existing Table object. <br>
	 * <br>
	 * The name of the table must be alphanumeric and unique per database.<br>
	 * 
	 * @param tableName The name of the existing table.
	 * @param newTableName The new name for the table.
	 * @throws DDLException TODO
	 * @see metadata.Database
	 * @see metadata.Table
	 */
	public void alterTableName(
			String tableName,
			String newTableName) throws DDLException {
		
		checkIfRenameNotIneffective(tableName, newTableName, "Table");
		
		Table table = getTableCheckIfExists(tableName);
		
		checkIfNameValid(newTableName, "Table", PATTERN_DISSALOWED_CHARS_FOR_NAMES);
		checkIfObjectNotAlreadyExists(newTableName, "Table");
		
		table.setName(newTableName);
		database.refreshTableName(tableName);
	}

	/**
	 * Renames an existing Column object. <br>
	 * <br>
	 * The name of the column must be alphanumeric and unique per table.<br>
	 * 
	 * @param tableName The name of the table where the column is located
	 * @param columnName The name of the existing column.
	 * @param newColumnName The new name for the column.
	 * @throws DDLException TODO
	 * @see metadata.Table
	 * @see metadata.Column
	 */
	public void alterColumnName(
			String tableName,
			String columnName,
			String newColumnName) throws DDLException {
		
		checkIfRenameNotIneffective(columnName, newColumnName, "Column");
		
		Table table = getTableCheckIfExists(tableName);
		Column column = getColumnCheckIfExists(table, columnName);
		
		checkIfNameValid(newColumnName, "Column", PATTERN_DISSALOWED_CHARS_FOR_NAMES);
		checkIfObjectNotAlreadyExistsInTable(table, newColumnName, "Column");
		
		column.setName(newColumnName);
		table.refreshColumnName(columnName);
	}
	
	/****************\
	|* DROP METHODS *| 
	\****************/
	
	/**
	 * Drops an existing table from the database. <br>
	 * <br>
	 * Dropping a table will also drop all associated objects of the following classes:<br>
	 * <ul>
	 * <li>Column</li>
	 * <li>Key</li>
	 * <li>Foreign Key</li>
	 * </ul>
	 * <br>
	 * Note that only Foreign Keys that span across columns from the dropped table will be dropped.<br>
	 * Should 1 or more existing Foreign Key objects point to a key from the dropped table, <br>
	 * this command will be halted before dropping any object.<br>
	 * 
	 * @param tableName The name of the existing table to drop.
	 * @throws DDLException TODO
	 * @see metadata.Table
	 */
	public void dropTable(
			String tableName) throws DDLException {
		
		Table table = getTableCheckIfExists(tableName);
		
		Set<String> allForeignKeyNames = database.getAllForeignKeyNames();
		for(String foreignKeyName : allForeignKeyNames) {
			checkIfForeignKeyNotReferringTable(table, foreignKeyName);
		}
		
		for(String foreignKeyName : allForeignKeyNames) {
			database.getForeignKey(foreignKeyName).getIndex().removeForeignIndex(foreignKeyName);
		}
		
		database.removeForeignKeys(table.getAllForeignKeyNames());
		database.removeKeys(table.getAllKeyNames());
		database.removeTable(tableName);
	}
	
	/**
	 * Drops an existing key from it's associated table and the database. <br>
	 * <br>
	 * Dropping a key should not be possible if there are foreign keys pointing at the key.<br>
	 * <br>
	 * If the key is a Primary Key appointed by it's associated Table, the key will still be dropped.<br>
	 * In that case the table will no longer have a primary key.<br>
	 * 
	 * @param keyName The name of the key to drop
	 * @throws DDLException TODO
	 * @see metadata.Database
	 * @see metadata.Table
	 * @see metadata.Key
	 */	
	public void dropKey(
			String tableName,
			String keyName) throws DDLException {
		
		Table table = getTableCheckIfExists(tableName);
		Key key = getKeyCheckIfExists(keyName);
		checkIfKeyExistsInTable(table, keyName);
		
		Set<String> allForeignKeyNames = database.getAllForeignKeyNames();
		for(String foreignKeyName : allForeignKeyNames) {
			checkIfForeignKeyNotReferringKey(key, foreignKeyName);
		}
		
		if(table.getPrimaryKey() != null) 
			if(table.getPrimaryKey().getName().equals(key.getName()))
				table.setPrimaryKey(null);
		
		table.removeKey(key.getName());
		database.removeKey(key.getName());
	}
	
	/**
	 * Drops an existing foreign key from it's associated table and the database. <br>
	 * 
	 * @param foreignKeyName The name of the key to drop
	 * @throws DDLException TODO
	 * @see metadata.Database
	 * @see metadata.Table
	 * @see metadata.Key
	 * @see metadata.ForeignKey
	 */		
	public void dropForeignKey(
			String tableName,
			String foreignKeyName) throws DDLException {
		
		Table table = getTableCheckIfExists(tableName);
		ForeignKey foreignKey = getForeignKeyCheckIfExists(foreignKeyName);
		checkIfForeignKeyExistsInTable(table, foreignKeyName);
		
		foreignKey.getIndex().removeForeignIndex(foreignKeyName);
		table.removeForeignKey(foreignKeyName);
		database.removeForeignKey(foreignKeyName);
		
	}
	
	/**
	 * Removes the appointed Primary Key from an existing table. <br>
	 * <br>
	 * Calling this method will not drop the key entirely from the table; this is merely to "remove primary key status".<br>
	 * <br>
	 * To drop a primary key entirely from it's associated table, call the method dropKey() instead.<br>
	 * This method (dropPrimaryKey()) should not be needed before doing so;<br>
	 * the method dropKey() should autonomously remove Primary Key status on the existing key that is to be dropped.<br>
	 * 
	 * @param tableName The name of the table to no longer have a Primary Key appointed
	 * @throws DDLException TODO
	 * @see metadata.Database
	 * @see metadata.Table
	 * @see metadata.Key
	 */		
	public void dropPrimaryKey(
			String tableName) throws DDLException {
		
		Table table = getTableCheckIfExists(tableName);
		checkIfTableHasAPrimaryKey(table);
		
		table.setPrimaryKey(null);
		
	}
	
	
	//DML (Data Manipulation Language)
	
	//private methods for error checks
	private Table getTableCheckIfFound(String tableName) throws DMLException {
		Table table = database.getTable(tableName);
		
		if(!database.hasTable(tableName))
			throw new TableNotFoundException("Table name \""+tableName+"\" does not exist");
		
		if(table == null)
			throw new DatabaseInconsistencyException("Table name \""+tableName+"\" exists but table object doesn't");
		
		return table;
	}
	
	private Column getColumnCheckIfFound(Table table, String columnName) throws DMLException {
		Column column = table.getColumn(columnName);
		
		if(!table.hasColumn(columnName))
			throw new ColumnNotFoundException("Column name \""+columnName+"\" does not exist");
		
		if(column == null)
			throw new DatabaseInconsistencyException("Column name \""+columnName+"\" exists but column object doesn't");
		
		return column;
	}
	
	private void checkIfValuesArraySizeCorrespondsToColumnCount(String[] valuesArray, int columnCount) throws DMLException {
		if(valuesArray.length != columnCount)
			throw new InsertValuesArraySizeException("Incorrect amount of values. Expected "+columnCount+", found "+valuesArray.length);
	}
	
	private void checkIfValueDatatypeValidForColumn(Column column, String value) throws DMLException {
		switch (column.getDatatype()) {
		case SQL.DATATYPE_BOOLEAN:
			value = value.toUpperCase();
			if(value != SQL.TOKEN_VALUE_BOOLEAN_FALSE &&
					value != SQL.TOKEN_VALUE_BOOLEAN_TRUE)
				throw new InvalidDatatypeException("Value \""+value+"\" for column\""+column.getName()+"\" is not a boolean");
			break;
		case SQL.DATATYPE_INT:
			try {
				Integer.parseInt(value);
			} catch (NumberFormatException e) {
				throw new InvalidDatatypeException("Value \""+value+"\" for column\""+column.getName()+"\" is not an integer");
			}
			break;
		case SQL.DATATYPE_STRING:
			break;
		default: 
			throw new DatabaseInconsistencyException("Column \""+column.getName()+"\" datatype \""+column.getDatatype()+"\" unrecognized");
		}
	}
	
	private int getColumnsRowCountCheckIfConsistent(Column[] columns) throws DMLException {
		int rowCount = columns[0].getRowCount();
		for(Column column : columns) {
			if(rowCount != column.getRowCount())
				throw new DatabaseInconsistencyException("Rowcount("+column.getRowCount()+") of \""+column.getName()+"\" was not as expected ("+rowCount+")");
		}
		return rowCount;
	}

	private void checkIfColumnNewRowCountConsistent(Column[] columns, int suggestedAmountAdded, int oldColumnRowCount) throws DMLException{
		for(Column column : columns) {
			if(column.getRowCount()-suggestedAmountAdded != oldColumnRowCount)
				throw new InsertOperationCriticalFailureException("Not all values were added, first missing value(s) found in column \""+column.getName()+"\"");
		}
	}
	
	private HashMap<Integer, Integer> getKeyColMappingCheckIfExists(Column[] columns, Key key) throws DMLException {
		HashMap<Integer, Integer> columnMappingForKey = new HashMap<Integer, Integer>();
		Column[] keyColumns = key.getColumnsOrdered();
		
		for(int j = 0; j < keyColumns.length; ++j) {
			int columnPos = Arrays.asList(columns).indexOf(keyColumns[j]);
			
			if(columnPos < 0)
				throw new KeyConstraintException("Cannot find column \""+keyColumns[j].getName()+"\" in insert statement for Key \""+key.getName()+"\"");
			
			columnMappingForKey.put(j, columnPos);
		}
		
		return columnMappingForKey;
	}
	
	private HashMap<Integer, Integer> getForeignKeyColMappingCheckIfExists(Column[] columns, ForeignKey foreignKey) throws DMLException {
		HashMap<Integer, Integer> columnMappingForKey = new HashMap<Integer, Integer>();
		Column[] keyColumns = foreignKey.getColumnsOrdered();
		
		for(int j = 0; j < keyColumns.length; ++j) {
			int columnPos = Arrays.asList(columns).indexOf(keyColumns[j]);
			
			if(columnPos < 0)
				throw new KeyConstraintException("Cannot find column \""+keyColumns[j].getName()+"\" in insert statement for Foreign Key \""+foreignKey.getName()+"\"");
			
			columnMappingForKey.put(j, columnPos);
		}
		
		return columnMappingForKey;
	}
	
	private void checkIfKeyConstraintAllowsInsert(Key key, List<String> values) throws DMLException {
		Integer indexEntry = key.getIndex().getIndexEntry(values);
		
		if(indexEntry != null)
			throw new KeyConstraintException("Key \""+key.getName()+"\" preventing duplicate entry");
	}
	
	private void checkIfForeignKeyConstraintAllowsInsert(ForeignKey foreignKey, List<String> values) throws DMLException {
		Integer indexEntry = foreignKey.getIndex().getIndexEntry(values);
		
		if(indexEntry == null)
			throw new KeyConstraintException("Foreign Key \""+foreignKey.getName()+"\" preventing entry without existing foreign values");
	}
	
	private void checkIfWhereClauseSyntaxCorrect(Table table, String[][] whereClause) throws DMLException {
		if(whereClause.length % 2 != 1)
			throw new WhereClauseException("Where clause syntax incorrect, refer to RDBMS.select() documentation for proper usage");

		for(int i = 0; i < whereClause.length; ++i) {
			if(i % 2 == 0 && whereClause[i].length == 3) { 
				if(!table.getAllColumnNames().contains(whereClause[i][0]))
					throw new WhereClauseException("Column name \""+whereClause[i][0]+"\" not found in table \""+table.getName()+"\"");
				
				else if(! table.getColumn(whereClause[i][0]).getDatatype().equals(SQL.DATATYPE_INT)
						&& !whereClause[i][1].equals(SQL.EXPRESSION_OPERATOR_EQUALTO)
						&& !whereClause[i][1].equals(SQL.EXPRESSION_OPERATOR_NOT_EQUALTO) )
					throw new WhereClauseException("Can not use operator \""+whereClause[i][1]+"\" "+
							"for non-integer type column \""+whereClause[i][0]+"\"");

			} else if(i % 2 == 1 && whereClause[i].length == 1) {
				
			} else {
				throw new WhereClauseException("Where clause syntax incorrect, refer to RDBMS.select() documentation for proper usage");
			}
		}
	}
	
	private void checkIfDeleteNotConstrainedByForeignKeys(Table table, Set<Integer> rowNumbers) throws DMLException {
		for(String keyName : table.getAllKeyNames()) {
			Key key = table.getKey(keyName);
			HashBucket index = key.getIndex();
			
			for(Integer rowNumber : rowNumbers) {
				if(index.getForeignIndexes(key.getValuesAt(rowNumber)).size() > 0)
					throw new ForeignKeyConstraintException("Cannot delete row, constrained by foreign key(s): ");
			}
		}
	}
	
	/***************\
	|* DML METHODS *| 
	\***************/
	
	/**
	 * Inserts 1 or multiple rows of values into an existing table. <br>
	 * <br>
	 * Any column part of the table but omitted from the array,<br>
	 * will result in entries added to the rows to be inserted,<br>
	 * having either a null value entered or the default value entered,<br>
	 * depending on the notnull specification of the omitted column.<br>
	 * The default value for a column with notnull=true depends on the datatype specified,<br>
	 * which can be found as a static final member of the Column class starting with "DEFAULT_VALUE_".<br> 
	 * <br>
	 * The value matrix should have a makeup of an array of rows of values.<br>
	 * Every row of values (Object[]) must have same size as that of the columnNames array.<br>
	 * Also, the datatypes of the values in each row must match that of the columns respectively.<br>
	 * <br>
	 * An example would be: {{"Lorem Ipsum", 3, false}, {"Ipsum Lorem", 11, true}}.<br>
	 * <br>
	 * values[0] would be the first row and consist of an array of values, in this case {"Lorem Ipsum", 3, false}.<br>
	 * This example would only be possible if the 3 chosen columns have the datatypes "str", "int" and "bln" respectively.<br>
	 * <br>
	 * TODO: warn about keys(no duplicates or inserting null in keys) and foreign keys(no values inserting that don't relate)<br>
	 * 
	 * @param tableName The name of the existing table.
	 * @param columnNames The names of the columns respectively associated with the given data.
	 * @param values The values to insert into the database.
	 * @throws InvalidDatatypeException Thrown when the datatype of 1 or more values does not correspond to the datatype of the corresponding column.
	 * @throws DMLException TODO
	 */
	public void insert(
			String tableName,
			String[] columnNames,
			String[][] values
			) throws DMLException {
		
		//Checks if referred metadata exists
		Table table = getTableCheckIfFound(tableName);
		
		Column[] columns = new Column[columnNames.length];
		Set<String> columnsUnused = table.getAllColumnNames();
		
		for(int i = 0; i < columnNames.length; ++i) {
			Column column = getColumnCheckIfFound(table, columnNames[i]);
			
			columns[i] = column;
			columnsUnused.remove(columnNames[i]);
		}
		
		//Checks if data values are valid for corresponding columns, set them up for insertion
		for(int i = 0; i < values.length; ++i) {
			checkIfValuesArraySizeCorrespondsToColumnCount(values[i], columns.length);
			
			for(int j = 0; j < columns.length; ++j) {		
				checkIfValueDatatypeValidForColumn(columns[j], values[i][j]);
			}
		}
		
		//Checks if Key constraints are restricting the insertion
		Set<String> keyNames = table.getAllKeyNames();
		for(String keyName : keyNames) {
			Key key = table.getKey(keyName);
			
			//Map out which column every keyColumn corresponds to
			Map<Integer, Integer> columnMappingForKey = getKeyColMappingCheckIfExists(columns ,key);
			
			for(int i = 0; i < values.length; ++i) {
				List<String> newRow = new ArrayList<String>();
				
				for(int j = 0; j < columnMappingForKey.size(); ++j) {
					newRow.add(values[i][columnMappingForKey.get(j)]);
				}
				checkIfKeyConstraintAllowsInsert(key, newRow);
			}
		}
		
		//Checks if Foreign Key constraints are restricting the insertion
		Set<String> foreignKeyNames = table.getAllForeignKeyNames();
		for(String foreignKeyName : foreignKeyNames) {
			ForeignKey foreignKey = table.getForeignKey(foreignKeyName);
			
			Map<Integer, Integer> columnMappingForKey = getForeignKeyColMappingCheckIfExists(columns, foreignKey);
			
			for(int i = 0; i < values.length; ++i) {
				List<String> newRow = new ArrayList<String>();
				
				for(int j = 0; j < columnMappingForKey.size(); ++j) {
					newRow.add(values[i][columnMappingForKey.get(j)]);
				}
				
				checkIfForeignKeyConstraintAllowsInsert(foreignKey, newRow);
			}
		}
		
		//Checks for database consistency in existing data before adding new data
		int oldColumnRowCount = getColumnsRowCountCheckIfConsistent(columns);
		
		//data insertion
		for(int i = 0; i < values.length; ++i) {
			for(int j = 0; j < columns.length; ++j) {
				columns[j].insertValue(values[i][j]);
			}
			for(String columnName : columnsUnused) {
				Column unusedColumn = table.getColumn(columnName);
				unusedColumn.insertValue(unusedColumn.getDefaultValue());
			}
		}
		
		//Checks for database consistency in existing data after adding new data
		checkIfColumnNewRowCountConsistent(columns, values.length, oldColumnRowCount);
		
		
		//updating the indexes
		for(String keyName : keyNames) {
			Key key = table.getKey(keyName);
			key.indexAtFor(oldColumnRowCount, values.length);
		}
		for(String foreignKeyName : foreignKeyNames) {
			ForeignKey foreignKey = table.getForeignKey(foreignKeyName);
			foreignKey.indexAtFor(oldColumnRowCount, values.length);
		}
	}

	/**
	 * Retrieves data from the database. <br>
	 * <br>
	 * From an existing table, chose the columns of which data should be shown.<br>
	 * <br>
	 * The whereClause has a "Left to right" priority. To give an example what this means, study this example:<br>
	 * {{"Sales",">","10"},{"AND"},{"Sales","<","20"},{"OR"},{"Seller","=","Peter"},{"AND"},{"Sales","<","25"}}.<br>
	 * Values in the "Sales" column have to be higher than 10, otherwise the data is discarded.<br> 
	 * If that is the case, the next requirement is that either those same values must be lower than 20,<br>
	 * or the value in the "Seller" column must be "Peter" in which case the "Sales" values must be lower than 25 instead.<br>
	 * <br>
	 * This immediately displays the allowed structure of this matrix:<br>
	 * Every even position (0, 2, 4, etc.) has a String array of size 3,<br>
	 * every odd position (1, 3, 5, etc.) has a String array of size 1.<br>
	 * Anything else causes a WhereClauseException. Same goes for the following situations:<br>
	 * <ul>
	 * 	<li>Contents of the single element in the array at an odd position in the matrix,<br>
	 *		that does not correspond to any of the static DML_SELECT_WHERE_CLAUSE_OPERATOR_ attributes.</li>
	 *	<li>Contents of the second element in the array at an even position in the matrix,<br>
	 *		that does not correspond to any of the static DML_SELECT_WHERE_EXPRESSION_OPERATOR_ attributes.</li>
	 *	<li>Contents of the first element in the array at an even position in the matrix,<br>
	 *		that does not correspond to existing columns in the chosen table.</li>
	 *	<li>Contents of the third and last element in the array at an even position in the matrix,<br>
	 *		of which the datatype does not correspond to that of the column of which it is being compared to.</li>
	 * </ul>
	 * From this can be also be concluded that every expression in the where clause must start with a column name,<br>
	 * and that the values from a column can ONLY be compared to a value directly from the right side of the expression.<br>
	 * Also note that non-integer type values can ONLY be compared with the operators "=" and "!=".<br>
	 * 
	 * @param tableName The name of the existing table.
	 * @param columnNames The columns of which data should be selected.
	 * @param whereClause The where clause ("syntax" described in method description).
	 * @throws WhereClauseException Thrown when the whereClause array causes errors.
	 * @throws DMLException TODO
	 */
	public String[][] select (
			String tableName,
			String[] columnNames,
			String[][] whereClause
			) throws DMLException {
		
		List<String[]> selectedData = new ArrayList<String[]>();
		Set<Integer> rowNumbers = new HashSet<Integer>();
		
		//Checks if referred metadata exists
		Table table = getTableCheckIfFound(tableName);
		
		//Get the columns, all if none are specified
		Column[] columns;
		if(columnNames.length == 0) {
			String[] allColumnNames = table.getAllColumnNames().toArray(new String[0]);	
			columns = new Column[allColumnNames.length];
			
			for(int i = 0; i < allColumnNames.length; ++i) {
				columns[i] = table.getColumn(allColumnNames[i]);
			}
		} else {
			columns = new Column[columnNames.length];
		
			for(int i = 0; i < columnNames.length; ++i) {
				Column column = getColumnCheckIfFound(table, columnNames[i]);
				
				columns[i] = column;
			}
		}
	
		//get the positions of the rows
		if(whereClause.length > 0) {
			checkIfWhereClauseSyntaxCorrect(table, whereClause);
			
			rowNumbers = executeWhereClause(table, Arrays.asList(whereClause));
		} else  {
			for(int rowNumber = 0; rowNumber < columns[0].getRowCount(); ++rowNumber) {
				rowNumbers.add(rowNumber);
			}
		}
		
		//retrieve the data based on resolved rownumbers
		for(Integer rowNumber : rowNumbers) {
			String[] data = new String[columns.length];
			for(int i = 0; i < columns.length; ++i) {
				data[i] = columns[i].getValue(rowNumber);
			}
			selectedData.add(data);
		}
		
		return selectedData.toArray(new String[0][0]);
		
	}
	
	/**
	 * Deletes 1 or multiple rows of values from an existing table. <br>
	 * <br>
	 * The where clause should work exactly the same as in the RDBMS.select() method.<br>
	 * 
	 * @param tableName The name of the existing table.
	 * @param whereClause The where clause ("syntax" described in method description).
	 * @throws WhereClauseException Thrown when the whereClause array causes errors.
	 * @throws DMLException TODO
	 */
	public void delete(
			String tableName,
			String[][] whereClause
			) throws DMLException {
		
		Set<Integer> rowNumbers = new HashSet<Integer>();
		
		//Checks if referred metadata exists
		Table table = getTableCheckIfFound(tableName);
		
		//get the positions of the rows
		if(whereClause.length > 0) {
			checkIfWhereClauseSyntaxCorrect(table, whereClause);
			
			rowNumbers = executeWhereClause(table, Arrays.asList(whereClause));
		} else {
			for(int rowNumber = 0; rowNumber < table.getColumn(table.getAllColumnNames().iterator().next()).getRowCount(); ++rowNumber) {
				rowNumbers.add(rowNumber);
			}
		}
		
		//constraint checks
		checkIfDeleteNotConstrainedByForeignKeys(table, rowNumbers);
		
		//delete the rows
		for(Integer rowNumber : rowNumbers) {
			//removing index entries
			for(String keyName : table.getAllKeyNames()) {
				Key key = table.getKey(keyName);
				
				key.removeIndexAt(rowNumber);
			}
			for(String columnName : table.getAllColumnNames()) {
				Column column = table.getColumn(columnName);
				
				column.removeValue(rowNumber);
			}
		}
	}

	private Set<Integer> executeWhereClause(
			Table table,
			List<String[]> whereClause) throws WhereClauseException {
		
		Set<Integer> valuesFound = new HashSet<Integer>();
		
		Column column = table.getColumn(whereClause.get(0)[0]);
		String expressionOperator = whereClause.get(0)[1];
		String compareValue = whereClause.get(0)[2];
		
		Integer operationalDepth = null;
		HashBucket index = null;
		
		for(String keyName : table.getAllKeyNames()) {
			Key key = table.getKey(keyName);
			
			//TODO find better method of choosing a key for executing a where expression, now stops at first hit
			if(key.getColumnsUnordered().contains(column)) {
				Column[] keyColumns = key.getColumnsOrdered();
				
				for(int i = 0; i < keyColumns.length; ++i) {
					if(keyColumns[i] == column)
						operationalDepth = keyColumns.length-i;
				}
				
				index = key.getIndex();
				
				break;
			}
		}
		
		if(index == null && operationalDepth == null) {
			System.out.println("WARNING: No existing index compatible with where clause ("+Arrays.toString(whereClause.get(0))+"), cannot retrieve results");
		} else {
			valuesFound.addAll(executeWhereExpression(index,
					operationalDepth,
					expressionOperator,
					compareValue));
		}
		
		//next where clause expression, if any
		if(whereClause.size() > 1) {
			String whereClauseOperator = whereClause.get(1)[0];
			
			List<String[]> nextWhereClause = new ArrayList<String[]>(whereClause);
			nextWhereClause.remove(0);
			nextWhereClause.remove(0);
			
			switch (whereClauseOperator) {
				case SQL.WHERE_CLAUSE_OPERATOR_AND :
					if(valuesFound.size() > 0) //no need to perform AND operation if one side already has "false"
						valuesFound.retainAll(executeWhereClause(table, nextWhereClause));
					break;
					
				case SQL.WHERE_CLAUSE_OPERATOR_OR :
					valuesFound.addAll(executeWhereClause(table, nextWhereClause));
					break;
					
				default :
					throw new  WhereClauseException("Token \""+whereClauseOperator+"\" is not a valid where clause operator");
			}
		}
		return valuesFound;
	}
	
	private Set<Integer> executeWhereExpression(
			HashBucket index,
			Integer operationalDepth,
			String expressionOperator,
			String compareValue) throws WhereClauseException {
		
		Set<Integer> results = new HashSet<Integer>();
		if(index.depth == operationalDepth) {
			for(Map.Entry<String, HashBucket> indexEntry : index) {
				switch (expressionOperator) {
					case SQL.EXPRESSION_OPERATOR_BIGGERTHAN:
						if(Integer.valueOf(indexEntry.getKey()) > Integer.valueOf(compareValue))
							results.addAll(indexEntry.getValue().getAllIndexEntries());
						break;
					case SQL.EXPRESSION_OPERATOR_BIGGERTHANOREQUAL:
						if(Integer.valueOf(indexEntry.getKey()) >= Integer.valueOf(compareValue))
							results.addAll(indexEntry.getValue().getAllIndexEntries());
						break;
					case SQL.EXPRESSION_OPERATOR_SMALLERTHAN:
						if(Integer.valueOf(indexEntry.getKey()) < Integer.valueOf(compareValue))
							results.addAll(indexEntry.getValue().getAllIndexEntries());
						break;
					case SQL.EXPRESSION_OPERATOR_SMALLERTHANOREQUAL:
						if(Integer.valueOf(indexEntry.getKey()) <= Integer.valueOf(compareValue))
							results.addAll(indexEntry.getValue().getAllIndexEntries());
						break;
					case SQL.EXPRESSION_OPERATOR_EQUALTO:
						if(indexEntry.getKey().equals(compareValue))
							results.addAll(indexEntry.getValue().getAllIndexEntries());
						break;
					case SQL.EXPRESSION_OPERATOR_NOT_EQUALTO:
						if(!indexEntry.getKey().equals(compareValue))
							results.addAll(indexEntry.getValue().getAllIndexEntries());
						break;
					default:
						throw new WhereClauseException("Token \""+expressionOperator+"\" is not valid where expression operator");
				}
			}
		} else {
			for(Map.Entry<String, HashBucket> indexEntry : index) {
				results.addAll(executeWhereExpression(indexEntry.getValue(), operationalDepth, expressionOperator, compareValue));
			}
		}
		return results;
	}

}